use [Comunarte] 
go

------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Usuario_Tipo_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Usuario DROP CONSTRAINT FK_Usuario_Tipo_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Post_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Post DROP CONSTRAINT FK_Post_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Post_Categoria_Post') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Post DROP CONSTRAINT FK_Post_Categoria_Post
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Comentario_Post') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Comentario DROP CONSTRAINT FK_Comentario_Post
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Comentario_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Comentario DROP CONSTRAINT FK_Comentario_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Contacto_Seguidor') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Contacto DROP CONSTRAINT FK_Contacto_Seguidor
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Contacto_Seguido') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Contacto DROP CONSTRAINT FK_Contacto_Seguido
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Foto_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Foto DROP CONSTRAINT FK_Foto_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Notificacion_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Notificacion DROP CONSTRAINT FK_Notificacion_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Notificacion_Post') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Notificacion DROP CONSTRAINT FK_Notificacion_Post
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Notificacion_Notificado_Notificacion') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Notificacion_Notificado DROP CONSTRAINT FK_Notificacion_Notificado_Notificacion
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Notificacion_Notificado_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Notificacion_Notificado DROP CONSTRAINT FK_Notificacion_Notificado_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Bancar_Usuario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Bancar DROP CONSTRAINT FK_Bancar_Usuario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Bancar_Post') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Bancar DROP CONSTRAINT FK_Bancar_Post
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Bancar_Comentario') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Bancar DROP CONSTRAINT FK_Bancar_Comentario
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Bancar_Foto') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Bancar DROP CONSTRAINT FK_Bancar_Foto
GO

----------------------


-- Tabla Usuario --------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Usuario') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Usuario
GO


CREATE TABLE Usuario ( 
	id_usuario int identity(1,1)  NOT NULL,
	nombre varchar(50) NOT NULL,
	apellido varchar(50) NULL, 
	tipo_usuario_id int NOT NULL,
	mail varchar(50) NULL,
	pass varchar(15) NULL,
	url_foto_perfil varchar(max) NULL,
	id_facebook varchar(50) NULL,
	id_gmail varchar(200) NULL,
	id_twitter varchar(200) NULL,
	id_twitter_secret varchar(200) NULL,
	twitterIdentification decimal NULL
)
GO

ALTER TABLE Usuario ADD CONSTRAINT PK_Usuario
	PRIMARY KEY CLUSTERED (id_usuario)
GO

-- Tabla Tipo_Usuario --------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Tipo_Usuario') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Tipo_Usuario
GO


CREATE TABLE Tipo_Usuario ( 
	id_tipo_usuario int identity(1,1)  NOT NULL,
	desc_tipo_usuario varchar(30) NOT NULL
)
GO

ALTER TABLE Tipo_Usuario ADD CONSTRAINT PK_Tipo_Usuario
	PRIMARY KEY CLUSTERED (id_tipo_usuario)
GO

-- Tabla Categoria_Post --------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Categoria_Post') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Categoria_Post
GO


CREATE TABLE Categoria_Post ( 
	id_categoria_post int identity(1,1)  NOT NULL,
	desc_categoria varchar(30) NOT NULL
)
GO

ALTER TABLE Categoria_Post ADD CONSTRAINT PK_Categoria_Post
	PRIMARY KEY CLUSTERED (id_categoria_post)
GO

-- Tabla Post --------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Post') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Post
GO


CREATE TABLE Post ( 
	id_post int identity(1,1)  NOT NULL,
	titulo_post varchar(50) NOT NULL,
	text_post varchar(MAX) NOT NULL,
	usuario_id int NOT NULL,
	categoria_post_id int NOT NULL,
	fecha_creacion datetime NULL,
	fecha_modificacion datetime NULL,
)
GO

ALTER TABLE Post ADD CONSTRAINT PK_Post
	PRIMARY KEY CLUSTERED (id_post)
GO

-- Tabla Comentario --------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Comentario') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Comentario
GO


CREATE TABLE Comentario ( 
	id_comentario int identity(1,1)  NOT NULL,
	usuario_id int NOT NULL,
	texto_comentario varchar(MAX) NOT NULL,
	post_id int NOT NULL
)
GO

ALTER TABLE Comentario ADD CONSTRAINT PK_Comentario
	PRIMARY KEY CLUSTERED (id_comentario)
GO

-- Tabla Contacto --------------------------------------


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Contacto') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Contacto
GO


CREATE TABLE Contacto ( 
	id_contacto int identity(1,1)  NOT NULL,
	seguidor_id int NOT NULL,
	seguido_id int NOT NULL,
	fecha datetime,
	estado varchar(1) NULL
)
GO

ALTER TABLE Contacto ADD CONSTRAINT PK_Contacto
	PRIMARY KEY CLUSTERED (id_contacto)
GO


-- Tabla Foto --------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Foto') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Foto
GO


CREATE TABLE Foto ( 
	id_foto int identity(1,1)  NOT NULL,
	url_foto varchar(max) NOT NULL,
	usuario_id int NOT NULL,
	descripcion_foto varchar(max) NULL
)
GO

ALTER TABLE Foto ADD CONSTRAINT PK_Foto
	PRIMARY KEY CLUSTERED (id_foto)
GO


-- Tabla Notificacion

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Notificacion') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Notificacion
GO


CREATE TABLE Notificacion ( 
	id_notificacion int identity(1,1)  NOT NULL,
	usuario_id int NULL,
	post_id int NULL,
	codigo_evento varchar(100) NOT NULL
)
GO

ALTER TABLE Comunarte.dbo.Notificacion ADD CONSTRAINT PK_Notificacion
	PRIMARY KEY CLUSTERED (id_notificacion)
GO

---- Tabla Notificacion_Notificado

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Notificacion_Notificado') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Notificacion_Notificado
GO


CREATE TABLE Notificacion_Notificado ( 
	notificacion_id int NOT NULL,
	leida bit NOT NULL,
	notificado_id int NOT NULL
)
GO



-- Tabla Bancar

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Bancar') AND  OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Bancar
GO


CREATE TABLE Bancar ( 
	post_id int NULL,
	usuario_id int NOT NULL,
	comentario_id int NULL,
	foto_id int NULL
)
GO


-- Relaciones --------------------------------------

ALTER TABLE Bancar ADD CONSTRAINT FK_Bancar_Usuario 
	FOREIGN KEY (usuario_id) REFERENCES Usuario (id_usuario)
GO

----

ALTER TABLE Bancar ADD CONSTRAINT FK_Bancar_Foto 
	FOREIGN KEY (foto_id) REFERENCES Foto (id_foto)
GO

----

ALTER TABLE Bancar ADD CONSTRAINT FK_Bancar_Post 
	FOREIGN KEY (post_id) REFERENCES Post (id_post)
GO

----

ALTER TABLE Bancar ADD CONSTRAINT FK_Bancar_Comentario 
	FOREIGN KEY (comentario_id) REFERENCES Comentario (id_comentario)
GO

----

ALTER TABLE Notificacion_Notificado ADD CONSTRAINT FK_Notificacion_Notificado_Notificacion 
	FOREIGN KEY (notificacion_id) REFERENCES Notificacion (id_notificacion)
GO

----

ALTER TABLE Notificacion_Notificado ADD CONSTRAINT FK_Notificacion_Notificado_Usuario 
	FOREIGN KEY (notificado_id) REFERENCES Usuario (id_usuario)
GO

----
ALTER TABLE Notificacion ADD CONSTRAINT FK_Notificacion_Usuario 
	FOREIGN KEY (usuario_id) REFERENCES Usuario (id_usuario)
GO

----

ALTER TABLE Notificacion ADD CONSTRAINT FK_Notificacion_Post 
	FOREIGN KEY (post_id) REFERENCES Post (id_post)
GO

----

ALTER TABLE Usuario ADD CONSTRAINT FK_Usuario_Tipo_Usuario 
	FOREIGN KEY (tipo_usuario_id) REFERENCES Tipo_Usuario (id_tipo_usuario)
GO

----

ALTER TABLE Post ADD CONSTRAINT FK_Post_Usuario 
	FOREIGN KEY (usuario_id) REFERENCES Usuario (id_usuario)
GO

-----

ALTER TABLE Post ADD CONSTRAINT FK_Post_Categoria_Post 
	FOREIGN KEY (categoria_post_id) REFERENCES Categoria_Post (id_categoria_post)
GO

-----

ALTER TABLE Comentario ADD CONSTRAINT FK_Comentario_Post 
	FOREIGN KEY (post_id) REFERENCES Post (id_post)
GO

-----

ALTER TABLE Comentario ADD CONSTRAINT FK_Comentario_Usuario 
	FOREIGN KEY (usuario_id) REFERENCES Usuario (id_usuario)
GO

-------

ALTER TABLE Contacto ADD CONSTRAINT FK_Contacto_Seguidor 
	FOREIGN KEY (seguidor_id) REFERENCES Usuario (id_usuario)
GO

ALTER TABLE Contacto ADD CONSTRAINT FK_Contacto_Seguido
	FOREIGN KEY (seguido_id) REFERENCES Usuario (id_usuario)
GO

-------

ALTER TABLE Foto ADD CONSTRAINT FK_Foto_Usuario 
	FOREIGN KEY (usuario_id) REFERENCES Usuario(id_usuario)
GO
